﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using Model;

namespace DAL
{
    public class SchoolRepository
    {
        private string connectionString = "Data Source=test.db;Version=3;";

        // 添加学校
        public void AddSchool(School school)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                
                Console.WriteLine(conn.FileName);
                string query = "INSERT INTO School (SchoolName) VALUES (@name)";
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@name", school.SchoolName);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 删除学校
        public void DeleteSchool(int schoolId)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();

                // 启用外键支持
                using (var pragmaCmd = new SQLiteCommand("PRAGMA foreign_keys = ON;", conn))
                {
                    pragmaCmd.ExecuteNonQuery();
                }

                string query = "DELETE FROM School WHERE SchoolID = @id";
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@id", schoolId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 更新学校信息
        public void UpdateSchool(School school)
        {
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "UPDATE School SET SchoolName = @name WHERE SchoolID = @id";
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@name", school.SchoolName);
                    cmd.Parameters.AddWithValue("@id", school.SchoolID);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 根据学校名称进行模糊查询
        public List<School> SearchSchoolsByName(string name)
        {
            var schools = new List<School>();
            using (var conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                string query = "SELECT * FROM Class";
                if(name != "")
                {
                    query = "SELECT * FROM School WHERE SchoolName LIKE @name";
                }
                using (var cmd = new SQLiteCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@name", "%" + name + "%");
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var school = new School
                            {
                                SchoolID = Convert.ToInt32(reader["SchoolID"]),
                                SchoolName = reader["SchoolName"].ToString()
                            };
                            schools.Add(school);
                        }
                    }
                }
            }
            return schools;
        }
    }
}
